﻿
namespace DBUtility 
{
    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Collections;

    /// <summary>
    /// 处理的数据库操作的抽象类
    /// </summary>
    /// <remarks>
    /// 苏登刚 [2007-1-1]   
    /// </remarks>
    /// <remarks>
    /// 在开发过程中可以添加新方法
    /// </remarks>
    public abstract class DBHelper
    {
        //Database connection strings
        public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["SQLMainDBConnString"].ConnectionString;
        public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString;
        public static readonly string ConnectionStringMembership = ConfigurationManager.ConnectionStrings["SQLMembershipConnString"].ConnectionString;

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        public DBHelper()
        {

        }
        #endregion

        # region 常用数据操作方法

        #region 执行一条或者多条不带参数的SQL语句,返回第一行第一列,即一个对象
        /// <summary>
        /// 执行一条或者多条不带参数的SQL语句,返回第一行第一列,即一个对象
        /// </summary>
        /// <param name="cmdText">不带参数的SQL语句</param>
        /// <returns>返回第一行第一列,经类型转换后就可以生成你需要的数据</returns>
        public static object ExecuteScalar(string cmdText)
        { 
            using (SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(conn, cmd, cmdText, null);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }

        }
        #endregion

        #region 执行一条或者多条带参数的SQL语句,返回第一行第一列,即一个对象
        /// <summary>
        /// 执行一条或者多条带参数的SQL语句,返回第一行第一列,即一个对象
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="commandParameters">SqlParameter参数数组</param>
        /// <returns>返回第一行第一列,经类型转换后就可以生成你需要的数据</returns>
        public static object ExecuteScalar(string cmdText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction))
            {

                SqlCommand cmd = new SqlCommand();

                PrepareCommand(conn, cmd, cmdText, commandParameters);

                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }

        }
        #endregion

        #region 执行一条带SqlCommand参数的命令,该方法支持多条SQL语句同时操作,并支持事务处理,返回bool值
        /// <summary>
        /// 执行一条带SqlCommand参数的命令,该方法支持多条SQL语句同时操作,并支持事务处理,返回bool值
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <returns>返回第一行第一列,经类型转换后就可以生成你想要的数据,返回null表示出错了,自动回滚所有操作</returns>
        public static bool ExecuteScalar(SqlCommand cmd)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;

                SqlTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                try
                {
                    cmd.ExecuteScalar();
                    tran.Commit();
                    cmd.Parameters.Clear();
                    return true;
                }
                catch
                {
                    tran.Rollback();
                    return false;
                }
                finally
                {
                    conn.Close();
                }

            }

        }
        #endregion

        #region 执行不带参数的SQL语句,返回影响的行数,返回为0,则说明执行没有正确的结果
        /// <summary>
        /// 执行不带参数的SQL语句,返回影响的行数,返回为0,则说明执行没有正确的结果
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(conn, cmd, cmdText, null);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }

        }
        #endregion

        #region 执行带参数的SQL语句,返回影响的行数,返回为0,则说明执行没有正确的结果
        /// <summary>
        /// 执行带参数的SQL语句,返回影响的行数,返回为0,则说明执行没有正确的结果
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="commandParameters">SqlParameter参数数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(conn, cmd, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }

        }
        #endregion

        #region 执行带参数SQL语句,返回一个SqlDataReader,注意使用后要关闭返回的SqlDataReader
        /// <summary>
        /// 执行带参数SQL语句,返回一个SqlDataReader,注意使用后要关闭返回的SqlDataReader
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="commandParameters">SqlParameter参数数组</param>
        /// <returns>返回SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
        {

            SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction);
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(conn, cmd, cmdText, commandParameters);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;

        }
        #endregion

        #region 执行不带参数SQL语句,返回一个SqlDataReader,注意使用后要关闭返回的SqlDataReader
        /// <summary>
        /// 执行不带参数SQL语句,返回一个SqlDataReader,注意使用后要关闭返回的SqlDataReader
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <returns>返回SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string cmdText)
        {

            SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction);
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(conn, cmd, cmdText, null);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;

        }
        #endregion

        #endregion

        #region 获取DataSet 和DataTable

        #region 执行不带参数的查询语句,返回DataSet
        /// <summary>
        /// 执行不带的查询语句,返回DataSet
        /// </summary>
        /// <param name="selectCmdText">SQl语句</param>
        /// <returns>返回一个DataSet</returns>
        public static DataSet GetDataSet(string selectCmdText)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction))
            {
                DataSet ds = new DataSet();
                conn.Open();
                SqlDataAdapter adr = new SqlDataAdapter(selectCmdText, conn);
                adr.Fill(ds);
                return ds;
            }
        }
        #endregion

        #region 执行带参数的查询语句,返回DataSet
        /// <summary>
        /// 执行带的查询语句,返回DataSet
        /// </summary>
        /// <param name="selectCmdText">SQl语句</param>
        /// <param name="selectParameters">参数数组</param>
        /// <returns>返回一个DataSet</returns>
        public static DataSet GetDataSet(string selectCmdText, params SqlParameter[] selectParameters)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction))
            {

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(conn, cmd, selectCmdText, selectParameters);

                DataSet ds = new DataSet();
                SqlDataAdapter adr = new SqlDataAdapter(cmd);
                adr.Fill(ds);
                return ds;
            }

        }
        #endregion

        #region 执行不带参数的查询语句,返回DataTable
        /// <summary>
        /// 执行不带的查询语句,返回DataTable
        /// </summary>
        /// <param name="selectCmdText">SQl语句</param>
        /// <returns>返回一个DataTable</returns>
        public static DataTable GetDataTable(string selectCmdText)
        {

            return GetDataSet(selectCmdText).Tables[0];

        }
        #endregion

        #region 执行不带参数的查询语句,返回DataTable 第一行第一列的值
        /// <summary>
        ///  执行不带参数的查询语句,返回DataTable 第一行第一列的值
        /// </summary>
        /// <param name="selectCmdText">SQl语句</param>
        /// <returns>返回一个DataTable</returns>
        public static string GetDataFirst(string selectCmdText)
        {

            DataRow dr = GetDataRowFirst(selectCmdText);
            string strResult = "";
            if (dr != null)
            {
                strResult = dr[0].ToString();
            }
            return strResult;
        }
        #endregion

        #region 获取数据库中所有行第一列的值，用,隔开
        /// <summary>
        /// 获取数据库中所有行第一列的值，用,隔开
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static string GetSQLAll(string strSQL)
        {
            string strResult = "";
            if (strSQL == "") return "";
            try
            {
                DataTable dt = GetDataTable(strSQL);
                foreach (DataRow dr in dt.Rows)
                {
                    if (strResult != "")
                    {
                        strResult += ",";
                    }
                    strResult += dr[0].ToString();
                }
            }
            catch (Exception)
            {
            }
            return strResult;
        }
        #endregion

        #region 获取sql语句返回的记录数
        /// <summary>
        /// 获取sql语句返回的记录数
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static int GetSQLCount(string strSQL)
        {
            int nResult = 0;
            if (strSQL == "") return 0;
            try
            {
                DataTable dt = GetDataTable(strSQL);
                if (dt != null)
                {
                    nResult = dt.Rows.Count;
                }
            }
            catch (Exception)
            {
                return -1;
            }
            return nResult;
        }
        #endregion

        #region 执行不带参数的查询语句,返回DataTable 第一行的列
        /// <summary>
        /// 执行不带参数的查询语句,返回DataTable 第一行的列
        /// </summary>
        /// <param name="selectCmdText">SQl语句</param>
        /// <returns>返回一个DataRow</returns>
        public static DataRow GetDataRowFirst(string selectCmdText)
        {

            DataTable dt = GetDataSet(selectCmdText).Tables[0];
            DataRow drResult = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                drResult = dt.Rows[0];
            }
            return drResult;
        }
        #endregion

        #region 执行带参数的查询语句,返回DataTable
        /// <summary>
        /// 执行带的查询语句,返回DataTable
        /// </summary>
        /// <param name="selectCmdText">SQl语句</param>
        /// <param name="selectParameters">参数数组</param>
        /// <returns>返回一个DataTable</returns>
        public static DataTable GetDataTable(string selectCmdText, params SqlParameter[] selectParameters)
        {

            return GetDataSet(selectCmdText, selectParameters).Tables[0];

        }
        #endregion

        #endregion

        #region 准备SqlCommand的相关参数
        /// <summary>
        /// 准备SqlCommand的相关参数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="cmd"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private static void PrepareCommand(SqlConnection conn, SqlCommand cmd, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion

        #region 准备SqlCommand的参数
        /// <summary>
        /// 准备SqlCommand的参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="cmdParms"></param>
        public static void PrepareCommand(SqlCommand cmd, SqlParameter[] cmdParms)
        {

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion

        #region 取表主键字段的自增长ID值
        /// <summary>
        /// 取表主键字段的自增长ID值,注：表的主键字段名称必须为ID，不能取名为MainID或其他名称，
        /// 因存储过程在检验产生最大ID是否正确时会去取对应表中主键字段的最大值
        /// </summary>
        /// <param name="tableName">数据库中的表名</param>
        /// <returns>返回主键最大ID</returns>
        public static int GetMaxID(string tableName)
        {
            //int maxID = -1;

            using (SqlConnection conn = new SqlConnection(ConnectionStringLocalTransaction))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetTablePKID", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@TableName", tableName);

                SqlParameter paraMaxID = cmd.Parameters.Add("@MaxID", SqlDbType.Int);
                paraMaxID.Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();
                return Convert.ToInt32(cmd.Parameters["@MaxID"].Value);
            }
        }
        #endregion
 
    }
}
